#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use List::Compare;
use utf8;
use Encode;

#fonction de création de type_test
sub create_type_test{
	my @desc=@_;
	my $dbh=$desc[1];
	my @unite_mesure=('Choix corrects','Nombre essais');
	my @id_type_test;
	
	my $row=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description=(E'$desc[0]') AND unite_mesure=?");
	
	foreach my $elt(@unite_mesure){
		$row->execute($elt);
		while(my @unite=$row->fetchrow_array()){
			push(@id_type_test,$unite[0]);
		}
		$row->finish();
	}
	if(not @id_type_test){
		foreach my $elt(@unite_mesure){
			$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', '$desc[0]', '$elt')"); 
			$row->execute($elt);
			while(my @unite=$row->fetchrow_array()){
				push(@id_type_test,$unite[0]);
			}
			$row->finish();
		}
	}
	
	return @id_type_test;
}

#fonction qui permet de remplir les tables
sub create_test{ 
	#id_type_test, id_anim, datet, dbh, exp, result2
	my @tab=@_;

	my @id_test2;
	my @id_res2;
	my @id_type_test;
	my $id_anim;
	my $datet;
	my @res;
	my $dbh;
	my $exp;
	
	foreach (my $val=0; $val<=$#tab; $val++){
		if($val<2){
			push(@id_type_test,$tab[$val]);
		}
		elsif($val eq 2){
			$id_anim=$tab[$val];
		}
		elsif($val eq 3){
			$datet=$tab[$val];
		}
		elsif($val eq 4){
			$dbh=$tab[$val];
		}
		elsif($val eq 5){
			$exp=$tab[$val];
		}
		elsif($val>5){
			push(@res,$tab[$val]);
		}
	}
	
	# ************************************* Création test ************************************* #

	### verification que le test n'existe pas deja ###
	my $prep_test=$dbh->prepare("SELECT id_test FROM Test WHERE experimentateur='$exp' AND Test.id_type_test=? AND date_test=(E'$datet')");	

	foreach my $k(@id_type_test){
		$prep_test->execute($k);
		while (my @val=$prep_test->fetchrow_array()){
			push(@id_test2,$val[0]);
		}
		$prep_test->finish();
	}
	
	### ajout du test si il n'existe pas ###
	if(not @id_test2){
		foreach my $l(@id_type_test){
			$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$datet', '$exp', $l)");
			$prep_test->execute($l);
			while (my @val=$prep_test->fetchrow_array()){
				push(@id_test2,$val[0]);
			}
			$prep_test->finish();
			
		}
	}
	
			
	# ************************************* Création resultat_test ************************************* #
	
	### vérification que resultat_test n'existe pas ###
	my $prep_rtest=$dbh->prepare("SELECT id_res from resultat_test WHERE id_test=? and id_animal=$id_anim");
	foreach my $i(@id_test2){
		$prep_rtest->execute($i);
		while (my @val=$prep_rtest->fetchrow_array()){
			push(@id_res2,$val[0]);
		}
		$prep_rtest->finish();
	}
	
	if(not @id_res2){
		foreach my $l(@id_test2){
			$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($l, $id_anim)");
			$prep_rtest->execute($l);
			while (my @val=$prep_rtest->fetchrow_array()){
				push(@id_res2,$val[0]);
			}
			$prep_rtest->finish();
		}
	}
	
	# ************************************* Création resultat_test_simple ************************************* #
	
	my $prep_resultat=$dbh->prepare("SELECT id_res FROM resultat_test_simple WHERE mesure=? AND id_res=?");

	for(my $elt=1; $elt<=$#res; $elt++){
		for(my $elt2=0; $elt2<=$#id_res2; $elt2++){
			if( $elt eq $elt2){
				$prep_resultat->execute($res[$elt],$id_res2[$elt2]);
				my @val=$prep_resultat->fetchrow_array();
				my $size3=@val;
				$prep_test->finish();
				if($size3 eq 0 and $res[$elt] ne ''){ #si l'animal est resté au centre					
					$dbh->do("INSERT INTO Resultat_test_simple(id_res, mesure) VALUES($id_res2[$elt2], $res[$elt])");					
				}
			}
		}
	}	
	return 0;
}
	
my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
$exp=~s/_/\ /;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;
my %result;
my %infos;
my $microcebe="";
my $micro;

foreach($workbook->worksheets()){
    $worksheet=$_;

	my ($row_min,$row_max) = $worksheet->row_range();
	my ($col_min, $col_max) = $worksheet->col_range();

	for my $row (1 .. $row_max){ #parcours lignes
		my $date_test;
		my  @test;
		
		$micro=$microcebe;
		for my $col ($col_min .. $col_max){ #parcours colonnes
			if ($col eq 0) { #recup num microcebe
				my $cont=$worksheet->get_cell($row,$col);
				$microcebe=$cont->value();
			}
			if($microcebe){
				if($col eq 1){
					my $cont=$worksheet->get_cell($row,$col);
					$date_test=$cont->value();
				}
				elsif($col>0 and $col<5){
					my $cont=$worksheet->get_cell($row,$col);
					push(@test,$cont->value());
				}
			}
		}
		
		if ($micro ne $microcebe){
			if($micro ne ""){
				$result{$micro}={%infos};
			}
			%infos=();
		}
		
		if($date_test){
			$infos{$date_test}=\@test; #infos: cle=date_test valeur={ds/inv,correct_choices,nb_trials}
		}
		if($row eq $row_max){
			$result{$microcebe}={%infos};
		}
	}

	#~ my $util;
	#~ foreach $util (sort keys %result) {
		#~ print "$util : \n";
		#~ foreach my $util2 (sort keys %{$result{$util}}) {
			#~ print "$util2 -> $result{$util}{$util2}[0],  $result{$util}{$util2}[1],  $result{$util}{$util2}[2]\n";
		#~ }
	#~ }


	# ********************* INSERTION DONNEES DANS LA BASE **************************** #


	# connection a sql
	my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});
	# execute INSERT query

	# ************************************* Création type_test ************************************* #

	my @id_type_disc=&create_type_test('Discrimination',$dbh);
	my @id_type_inv=&create_type_test('Inversion',$dbh);

	foreach my $code (sort keys %result) { #$code =code_animal
		#recuperation du code_animal et de sa date de naissance =>creation de l'animal
		my $id_anim;
		
		# ************************************* Création animal ************************************* #
		
		### verification de l'existence de l'animal dans la base
		$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code')");
		
		if (not $id_anim){ # si l'animal n'existait pas
			$dbh->do("INSERT INTO Animal(code_animal) VALUES ($code)");
			$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$code')");
		}
			
		foreach my $key (sort keys %{$result{$code}}) {
			my @id_test2;
			my @result2;
			my @id_res2;
			my $datet=$key; #date du test
			
			for my $i(0 .. 2){
				push(@result2,$result{$code}{$key}[$i]); 
			}
			
			#modification de id_type_test en fonction du nombre de valeurs que contient result2
			if($result2[0] eq 'discrimination'){
				&create_test(@id_type_disc, $id_anim, $datet, $dbh, $exp, @result2);
			}
			elsif($result2[0] eq 'inversion'){
				&create_test(@id_type_inv, $id_anim, $datet, $dbh, $exp, @result2);
			}
			
			
		}
	}
	last if $_->get_name() eq 'Feuil1';
	$dbh->disconnect();
}

